# -*- coding: utf-8 -*-
import re
from openpyxl import load_workbook
import copy
# from .WordDB import WordSql
from WordDB import WordSql
import sys
import os
import pymysql



class DB_new():
    def __init__(self):
        self.userid = 0
        self.wdsql = None
        self.intcol = []
        self.coursestileyuan = []
        self.usertbcol = []
        self.stname = []
        self.courses = []
        self.templatesname = []
        self.coursepath = ''
        self.school_id = 0
    def initdata(self):
        self.wdsql = WordSql('127.0.0.1', 'root', 'zx.123456', 3306, 'word', 'utf8')
        self.usertbcol = ['userid', 'name', 'password', 'major']
        self.dictuserinfo = {
            "userid": 0,
            "name": "",
            "password": "",
            "major": "",
        }
        self.stname = ['通识课', '通识选修课', '学科基础课', '学科专业基础课', '专业选修课', '集中性实践', '第二课堂']
        self.coursepath = "d:/word/excleapp/word/coursexlsx/"
        self.templatesname = ['目标', '要求', '预期', '主干学科', '主要实践性教学环节', '专业核心课程']
        self.coursestileyuan = [
            ('课程序号', '课程代码', '课程名称', '学时数', '学分数', '理论', '课内实践', '课外实践', '线上', '开课学期', '周学时', '考核方式', '备注', '毕业要求'),
            ('课程序号', '课程代码', '课程名称', '学时数', '学分数', '理论', '课内实践', '课外实践', '线上', '开课学期', '周学时', '考核方式', '备注', '毕业要求'),
            ('课程序号', '课程代码', '课程名称', '学时数', '学分数', '理论', '课内实践', '课外实践', '开课学期', '周学时', '考核方式', '备注', '毕业要求'),
            ('课程序号', '课程代码', '课程名称', '学时数', '学分数', '理论', '课内实践', '课外实践', '开课学期', '周学时', '考核方式', '备注', '毕业要求'),
            ('课程序号', '课程代码', '课程名称', '学时数', '学分数', '理论', '课内实践', '课外实践', '开课学期', '周学时', '考核方式', '备注', '毕业', '分组统计'),
            ('序号', '课程代码', '课程名称', '实践周数', '学分数', '学期安排', '考核方式', '备注', '毕业要求'),
            ('序号', '课程代码', '课程名称', '实践周数', '学分数', '学期安排', '考核方式', '备注')]
        self.intcol = [8, 8, 7, 7, 7, 3, 3]
        dbcoursestile = []
        for i in self.coursestileyuan:
            tmp = []
            for j in i:
                tmp.append(j)
            dbcoursestile.append(tmp[1:])
        self.coursestile = dbcoursestile
    def createtbcourses(self):
        # sqlcreatetbuser = "create table if not exists user (userid int(10) auto_increment primary key,name char(20) not null," \
        #                   "password char(30) default '123456',major char(30))DEFAULT CHARSET=utf8;"
        # self.wdsql.create(sqlcreatetbuser)
        # tmp = self.templatesname
        # sqlcreatetbtemplates = "create table if not exists templates (templatesid int(10) auto_increment primary key," \
        #                        "major char(30)," + "{} TEXT default null," * (len(self.templatesname) - 1) + \
        #                        "{} TEXT default null)DEFAULT CHARSET=utf8;"
        # sqlcreatecoursetemplates = "create table if not exists coursetemplates (coursetemplatesid int (10) auto_increment primary key," \
        #                            "userid int(10),major char(30),coursepath TEXT,year int(10))default charset=utf8;"
        # self.wdsql.create(sqlcreatecoursetemplates)
        #
        # sqlcreateschool = "create table if not exists school(schoolid int(10) auto_increment primary key,userid int(10),mycollege char(30),college char(30));"
        # self.wdsql.create(sqlcreateschool)
        # sqlcreatetbtemplates = sqlcreatetbtemplates.format(tmp[0], tmp[1], tmp[2], tmp[3], tmp[4], tmp[5])
        # self.wdsql.create(sqlcreatetbtemplates)
        # for i in range(len(self.coursestileyuan)):
        #     sqlcreatetb = "create table if not exists " + self.stname[
        #         i] + "(major char(30),%s int(10) auto_increment primary key," + \
        #                   "%s char(30) default null," * 2 + "%s float(10) default 0," * (self.intcol[i]) + \
        #                   "%s char(30) default null," * (len(data[i]) - (4 + self.intcol[i])) + \
        #                   "%s char(50) default null)DEFAULT CHARSET=utf8;"
        #     self.wdsql.createone(sqlcreatetb, data[i])

        sql_create_school = "create table if not exists school(id int(10) auto_increment primary key," \
                          "school_name char(15) not null)DEFAULT CHARSET=utf8"

        sql_create_user = "create table if not exists user(id int(20)auto_increment primary key, " \
                          "name char(10)not null,password char(30) default '123456'," \
                          "school_id int(10) default null)DEFAULT CHARSET=utf8"

        sql_peiyang = "create table if not exists 培养方案汇总(id int(10)auto_increment primary key," \
                      "user_id int(10)not null,专业版本 int(5) not null)DEFAULT CHARSET=utf8"
        sql_major_information = "create table if not exists major_information(id int(10)auto_increment primary key," \
                                "peiyang_id int(10)not null,主干学科 varchar(633) not null,主要实践性教学 varchar(633) not null,专业核心 varchar(633) not null,培养要求 varchar(633) not null,培养预期 varchar(633) not null)DEFAULT CHARSET=utf8"
        sql_class = "create table if not exists class(id int(10) auto_increment primary key,class_name char(15)not null ,class_number varchar(10) default null , class_request varchar(633) default null" \
                    ",理论 int(10) default null,课内实践 int(10) default null, 课外实践  int(10) default null,学分时 int(10) default null, 学分数 float(10) default null," \
                    "线上 int(10) default null , 开课学期 int(4) default null ,周学时 int(4) default null ," \
                    "考核方式 varchar(10) default null , 备注 varchar(20)default null ,毕业要求 varchar(50) default null ," \
                    "class_Similar int(10)not null, class_essential_Similar int(10)not null,peiyang_id int(10) not null" \
                    ")DEFAULT CHARSET=utf8"
        # sql_class_Similar = "create table if not exists class_Similar(id int(10) auto_increment primary key, " \
        #                     "class_Similar char(20) not null)DEFAULT CHARSET=utf8"
        #
        # sql_class_essential_Similar="create table if not exists class_essential_Similar(id int(10) auto_increment primary key, " \
        #                     "classessential char(20) not null)DEFAULT CHARSET=utf8"


        try:

            self.wdsql.create(sql_create_school)
            self.wdsql.create(sql_create_user)
            self.wdsql.create(sql_peiyang)
            self.wdsql.create(sql_major_information)
            self.wdsql.create(sql_class)
            # self.wdsql.create(sql_class_Similar)
            # self.wdsql.create(sql_class_essential_Similar)

        except Exception as result:
            print("数据表格创建失败", result)


    def insert_user(self, name, password, school):
        try:
            sql_select = "select * from school where school_name = '{school}'".format(school=school)
            school_id = self.wdsql.get_onedict(sql_select)

            if school_id == None:
                self.insert_school(school)
                self.school_id = self.wdsql.get_onedict(sql_select)['id']
            else:
                self.school_id = school_id['id']
            sql_user = "replace user(name,password,school_id)values('{name}','{password}',{school_id})".format(name=name, password=password, school_id=self.school_id)

            self.wdsql.insert(sql_user)
            print(sql_user)

        except Exception as result:
            print("用户插入失败", result)

    # def install_peiyang(self,number):

    # def install_peiyang(self,version,user_id):
    #     try:
    #
    def insert_school(self, school):
        try:
            self.wdsql.connect()
            sql = "replace school(school_name)values('{}')".format(school)
            self.wdsql.insert(sql)
            print(sql)

        except Exception as result:
            print("学校插入失败", result)
            return -1







if __name__ == '__main__':
    wd = DB_new()
    wd.initdata()
    wd.createtbcourses()
    wd.insert_user("root", "123123","湘南学院")

    print(wd.courses)



